﻿using Ayatta.Service.Extension;
using Dapper;
using System;
using System.Linq;
using System.Text;
using Ayatta.Contract;
using Ayatta.Contract.Domain;
using System.Collections.Generic;

namespace Ayatta.Service
{
    public class InfrastructureService : BaseService, IInfrastructureService
    {

        #region Area

        public bool AreaCreate(Area o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Infrastructure.Table.Area)
            .Values(o.Id, o.ParentId, o.Name, o.Group, o.Country, o.PostalCode)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AreaUpdate(Area o)
        {
            var template = new SqlBuilder()
            .Update(Database.Infrastructure.Table.Area)
            .Set("[ParentId]={0}", o.ParentId)
            .Set("[Name]={0}", o.Name)
            .Set("[Group]={0}", o.Group)
            .Set("[Country]={0}", o.Country)
            .Set("[PostalCode]={0}", o.PostalCode)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool AreaDelete(string id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Infrastructure.Table.Area)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public Area AreaGet(string id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Infrastructure.Table.Area)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Query<Area>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<Area> AreaList(string parentId = null)
        {
            var template = new SqlBuilder()
                .Select("*")
                .From(Database.Infrastructure.Table.Area)
                .Where()
                .If(!string.IsNullOrEmpty(parentId), "ParentId={0}", parentId)
                .Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Query<Area>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<Area> AreaPagedList(int page = 1, int size = 50, string parentId = null, int? country = null)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Infrastructure.Table.Area)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.PagedList<Area>(template);
            }
        }

        #endregion

        #region LogisticsCompany

        public bool LogisticsCompanyCreate(LogisticsCompany o)
        {
            var template = new SqlBuilder()
            .Insert(Database.Infrastructure.Table.LogisticsCompany)
            .Values(o.Code, o.Name, o.Reg, o.Category, o.Priority, o.Status)
            .Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool LogisticsCompanyUpdate(LogisticsCompany o)
        {
            var template = new SqlBuilder()
            .Update(Database.Infrastructure.Table.LogisticsCompany)
            .Set("[Code]={0}", o.Code)
            .Set("[Name]={0}", o.Name)
            .Set("[Reg]={0}", o.Reg)
            .Set("[Category]={0}", o.Category)
            .Set("[Priority]={0}", o.Priority)
            .Set("[Status]={0}", o.Status)
            .Where("[Id]={0}", o.Id).Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public bool LogisticsCompanyDelete(int id)
        {
            var template = new SqlBuilder()
            .Delete(Database.Infrastructure.Table.LogisticsCompany)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Execute(template.Sql, template.Param) == 1;
            }
        }

        public LogisticsCompany LogisticsCompanyGet(int id)
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Infrastructure.Table.LogisticsCompany)
            .Where("[Id]={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Query<LogisticsCompany>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        public IList<LogisticsCompany> LogisticsCompanyList()
        {
            var template = new SqlBuilder()
            .Select("*")
            .From(Database.Infrastructure.Table.LogisticsCompany)
            .Where("1=1").Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Query<LogisticsCompany>(template.Sql, template.Param).ToList();
            }
        }

        public PagedList<LogisticsCompany> LogisticsCompanyPagedList(int page = 1, int size = 20)
        {
            var builder = new RegexSqlBuilder().Field("*")
            .Table(Database.Infrastructure.Table.LogisticsCompany)
            .OrderBy("[Id] DESC")
            .AddParameters(new { begin = (page - 1) * size + 1, end = page * size });
            /*
            builder.Where("[Status]=@Status", new { status });
            */
            var template = builder.Complete(RegexSqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.PagedList<LogisticsCompany>(template);
            }
        }

        #endregion
    
        public Question QuestionGet(int id)
        {
            var template = new SqlBuilder()
                .Select("*")
                .From(Database.Infrastructure.Table.Question)
                .Where("Id={0}", id).Complete();

            using (var connection = GetConnection(ConnectionName.Infrastructure).EnsureOpen())
            {
                return connection.Query<Question>(template.Sql, template.Param).FirstOrDefault();
            }
        }

        #region IService
        public void CachePurge()
        {
            PurgeCache();
        }

        public PagedList<Magic<string, string, int>> CacheGet(int page = 1, int size = 50, int hitCount = 0)
        {
            return GetPagedCache(page, size, hitCount);
        }
        #endregion
    }
}
